// 博客分类模型
const { postgresPool, redisClient } = require('../config/database');

class BlogCategory {
    constructor(data) {
        this.id = data.id;
        this.name = data.name;
        this.slug = data.slug;
        this.description = data.description;
        this.parentId = data.parent_id;
        this.sortOrder = data.sort_order || 0;
        this.status = data.status || 'active';
        this.createdAt = data.created_at;
        this.updatedAt = data.updated_at;
    }

    // 创建分类
    static async create(categoryData) {
        const client = await postgresPool.connect();
        
        try {
            const query = `
                INSERT INTO blog_categories (name, slug, description, parent_id, sort_order, status)
                VALUES ($1, $2, $3, $4, $5, $6)
                RETURNING *
            `;
            
            const values = [
                categoryData.name,
                categoryData.slug || categoryData.name.toLowerCase().replace(/\s+/g, '-'),
                categoryData.description || '',
                categoryData.parentId || null,
                categoryData.sortOrder || 0,
                categoryData.status || 'active'
            ];
            
            const result = await client.query(query, values);
            const category = new BlogCategory(result.rows[0]);
            
            // 更新缓存
            await this.updateCache(category);
            
            return category;
        } finally {
            client.release();
        }
    }

    // 根据ID查找分类
    static async findById(id, useCache = true) {
        // 尝试从缓存获取
        if (useCache) {
            const cached = await redisClient.get(`blog_category:${id}`);
            if (cached) {
                return new BlogCategory(JSON.parse(cached));
            }
        }
        
        const client = await postgresPool.connect();
        
        try {
            const query = 'SELECT * FROM blog_categories WHERE id = $1 AND status = $2';
            const result = await client.query(query, [id, 'active']);
            
            if (result.rows.length === 0) {
                return null;
            }
            
            const category = new BlogCategory(result.rows[0]);
            
            // 更新缓存
            await this.updateCache(category);
            
            return category;
        } finally {
            client.release();
        }
    }

    // 更新分类
    async update(updateData) {
        const client = await postgresPool.connect();
        
        try {
            const fields = [];
            const values = [];
            let paramCount = 1;
            
            if (updateData.name !== undefined) {
                fields.push(`name = $${paramCount++}`);
                values.push(updateData.name);
            }
            if (updateData.slug !== undefined) {
                fields.push(`slug = $${paramCount++}`);
                values.push(updateData.slug);
            }
            if (updateData.description !== undefined) {
                fields.push(`description = $${paramCount++}`);
                values.push(updateData.description);
            }
            if (updateData.parentId !== undefined) {
                fields.push(`parent_id = $${paramCount++}`);
                values.push(updateData.parentId);
            }
            if (updateData.sortOrder !== undefined) {
                fields.push(`sort_order = $${paramCount++}`);
                values.push(updateData.sortOrder);
            }
            if (updateData.status !== undefined) {
                fields.push(`status = $${paramCount++}`);
                values.push(updateData.status);
            }
            
            if (fields.length === 0) {
                return this;
            }
            
            fields.push(`updated_at = CURRENT_TIMESTAMP`);
            values.push(this.id);
            
            const query = `
                UPDATE blog_categories 
                SET ${fields.join(', ')}
                WHERE id = $${paramCount}
                RETURNING *
            `;
            
            const result = await client.query(query, values);
            const updatedCategory = new BlogCategory(result.rows[0]);
            
            // 更新缓存
            await this.constructor.updateCache(updatedCategory);
            
            return updatedCategory;
        } finally {
            client.release();
        }
    }

    // 删除分类（软删除）
    async delete() {
        const client = await postgresPool.connect();
        
        try {
            // 检查是否有子分类
            const checkQuery = 'SELECT COUNT(*) FROM blog_categories WHERE parent_id = $1 AND status = $2';
            const checkResult = await client.query(checkQuery, [this.id, 'active']);
            
            if (parseInt(checkResult.rows[0].count) > 0) {
                throw new Error('无法删除包含子分类的分类');
            }
            
            // 检查是否有文章使用此分类
            const articleQuery = 'SELECT COUNT(*) FROM articles WHERE category = $1 AND status != $2';
            const articleResult = await client.query(articleQuery, [this.id, 'deleted']);
            
            if (parseInt(articleResult.rows[0].count) > 0) {
                throw new Error('无法删除有文章使用的分类');
            }
            
            // 软删除
            await this.update({ status: 'deleted' });
            
            // 清除缓存
            await redisClient.del(`blog_category:${this.id}`);
            
            return true;
        } finally {
            client.release();
        }
    }

    // 获取所有分类
    static async findAll(options = {}) {
        const client = await postgresPool.connect();
        
        try {
            const page = options.page || 1;
            const limit = options.limit || 100;
            const offset = (page - 1) * limit;
            
            const query = `
                SELECT * FROM blog_categories 
                WHERE status = $1
                ORDER BY sort_order ASC, created_at DESC
                LIMIT $2 OFFSET $3
            `;
            
            const countQuery = 'SELECT COUNT(*) FROM blog_categories WHERE status = $1';
            
            const [categoriesResult, countResult] = await Promise.all([
                client.query(query, ['active', limit, offset]),
                client.query(countQuery, ['active'])
            ]);
            
            return {
                categories: categoriesResult.rows.map(row => new BlogCategory(row)),
                total: parseInt(countResult.rows[0].count),
                page,
                limit,
                totalPages: Math.ceil(parseInt(countResult.rows[0].count) / limit)
            };
        } finally {
            client.release();
        }
    }

    // 获取分类树
    static async getCategoryTree() {
        const client = await postgresPool.connect();
        
        try {
            const query = `
                SELECT id, name, slug, description, parent_id, sort_order
                FROM blog_categories 
                WHERE status = 'active'
                ORDER BY sort_order ASC, created_at ASC
            `;
            
            const result = await client.query(query);
            const categories = result.rows;
            
            // 构建树形结构
            const categoryMap = new Map();
            const tree = [];
            
            // 创建映射
            categories.forEach(category => {
                categoryMap.set(category.id, { 
                    ...category, 
                    children: [] 
                });
            });
            
            // 构建树
            categories.forEach(category => {
                const node = categoryMap.get(category.id);
                if (category.parent_id === null) {
                    tree.push(node);
                } else {
                    const parent = categoryMap.get(category.parent_id);
                    if (parent) {
                        parent.children.push(node);
                    }
                }
            });
            
            return tree;
        } finally {
            client.release();
        }
    }

    // 更新缓存
    static async updateCache(category) {
        await redisClient.setex(
            `blog_category:${category.id}`,
            3600, // 1小时缓存
            JSON.stringify(category)
        );
    }

    // 获取分类统计
    static async getStats(categoryId) {
        const client = await postgresPool.connect();
        
        try {
            // 获取分类下的文章数量
            const articleCountQuery = `
                SELECT COUNT(*) FROM articles 
                WHERE category = $1 AND status = $2
            `;
            const articleCountResult = await client.query(
                articleCountQuery, 
                [categoryId, 'published']
            );
            
            // 获取子分类数量
            const subcategoryCountQuery = `
                SELECT COUNT(*) FROM blog_categories 
                WHERE parent_id = $1 AND status = $2
            `;
            const subcategoryCountResult = await client.query(
                subcategoryCountQuery,
                [categoryId, 'active']
            );
            
            return {
                articleCount: parseInt(articleCountResult.rows[0].count),
                subcategoryCount: parseInt(subcategoryCountResult.rows[0].count)
            };
        } finally {
            client.release();
        }
    }
}

module.exports = BlogCategory;